<?php

namespace App\Home\Models;

use App\Admin\Models\SystemMedia;
use Illuminate\Database\Eloquent\SoftDeletes;
use Illuminate\Support\Str;
use Illuminate\Support\Facades\DB;

class WorshipHymn extends Model
{
    use SoftDeletes;

    protected $guarded = []; 

public $fieldTypes = <<< EOF
{
    "id": "string",
    "name": "string",
    "description": "string",
    "worship_hymns": "App\\\\Admin\\\\Casts\\\\Json",
    "pic_url": "App\\\\Admin\\\\Casts\\\\Json",
    "play_count": "string",
    "hymn_count": "string",
    "created_at": "string",
    "updated_at": "string",
    "deleted_at": "string"
}
EOF;   

    public function __construct(){
        parent::__construct();
        $this->mergeCasts(json_decode($this->fieldTypes, true));
    }

    public function mp3()
    {
        return $this->hasOne(
            SystemMedia::class,
            'id',
            'mp3'
        );
    }

    public function lyric()
    {
        return $this->hasOne(
            SystemMedia::class,
            'id',
            'lyric'
        );
    }

    public function getSpectrum(array $ids)
    {
        return SystemMedia::query()->whereIn(
            'id',
            $ids
        )->pluck('path');
    }
    
    public function getHymnsByCollection($hymnIds){
        $fields = [
            "worship_hymns.id",
            "worship_hymns.artists",
            "worship_hymns.albums",
            "worship_albums.pic_url",
            DB::raw("group_concat(DISTINCT worship_artists.name) as artist"),
            DB::raw("group_concat(DISTINCT worship_albums.name) as album"),
            "worship_hymns.name",
            "worship_hymns.duration",
            "system_media.path as mp3",
            "worship_hymns.created_at"
        ];
        $hymnIdsStr = implode(',', $hymnIds);
        $list = self::query()
            ->leftJoin('system_media', 'system_media.id', '=', 'worship_hymns.mp3')
            ->leftJoin('worship_hymn_album', 'worship_hymn_album.hymn_id', '=', 'worship_hymns.id')
            ->leftJoin('worship_hymn_artist', 'worship_hymn_artist.hymn_id', '=', 'worship_hymns.id')
            ->leftJoin('worship_artists', 'worship_artists.id', '=', 'worship_hymn_artist.artist_id')
            ->leftJoin('worship_albums', 'worship_albums.id', '=', 'worship_hymn_album.album_id')
            ->whereIn('worship_hymns.id', $hymnIds)
            ->groupBy('worship_hymns.id')
            ->select($fields)
            ->orderByRaw(DB::raw("FIELD(worship_hymns.id, $hymnIdsStr)"))
            ->get();
        return $list;
    }

    public function getHymnsByName($name){
        $fields = [
            "worship_hymns.id",
            "worship_hymns.artists",
            "worship_hymns.albums",
            "worship_albums.pic_url",
            DB::raw("group_concat(DISTINCT worship_artists.name) as artist"),
            DB::raw("group_concat(DISTINCT worship_albums.name) as album"),
            "worship_hymns.name",
            "worship_hymns.duration",
            "system_media.path as mp3",
            "worship_hymns.created_at"
        ];
        $list = $this->query()
            ->leftJoin('system_media', 'system_media.id', '=', 'worship_hymns.mp3')
            ->leftJoin('worship_hymn_album', 'worship_hymn_album.hymn_id', '=', 'worship_hymns.id')
            ->leftJoin('worship_hymn_artist', 'worship_hymn_artist.hymn_id', '=', 'worship_hymns.id')
            ->leftJoin('worship_artists', 'worship_artists.id', '=', 'worship_hymn_artist.artist_id')
            ->leftJoin('worship_albums', 'worship_albums.id', '=', 'worship_hymn_album.album_id')
            ->where('worship_hymns.name', 'like', "%{$name}%")
            ->groupBy('worship_hymns.id')
            ->select($fields)
            ->paginate();
        return $list;
    }
}
